Variables are used to store values that can then be passed into tables for incremental loading, and injected into scripts, filters, and calculated columns in the Data Flow. They can also be passed into containers, processes, and dynamic expressions in the Master Flow.
The variable can be initialized from a static saved value, or it can be a dynamic value based on a database query. In the former case the initialization value is constant, while in the latter it is resolved only at runtime.
Note: this feature is available only with an Enterprise edition license.
A variable can be added to the following Data Flow nodes:
- Select nodes, where the variable is used for incremental loading.
- SQL Query, Python, R, and Calculated Column nodes, where the variable can be referenced into the script and used for incremental loading.
- Filter nodes, where the variable can be used as a filter value and for incremental loading.
Variables can be used in the following ways in the Data Flow:
- Incremental loading of tables to ensure that only new rows are searched during re-processing. This saves time and resources. Incremental loading is achieved by passing the variable to a Filter or Query node.
- Inject a variable value to a calculated column, which is particularly powerful when the variable is initialized from a database query.
- Inject variables into SQL, Python, and R scripts.
- Click here to learn about injecting variables into filters and scripts.
Variables are also used to perform a variety of functions in the Master Flow:
- Control looping of the master flow.
- Add a variable node to the master flow process.
- Load the results of a cmd command into a variable and then perform a process in the Master Flow based on the results.
- Inject a variable into a dynamic PQL expression and use that expression to call a Rest API.
- Load the value returned by a Rest API call into a variable.
- Inject a variable into a dynamic PQL expression used to distribute a message via email or SMS; the expression can be used to determine the distribution list, subject line, or message content.
- Inject a variable into a dynamic PQL expression used to create a conditional or switch function to execute a specified event based on the outcome of the variable condition.
Preview Variables
By default, Pyramid automatically creates a variable called IsPreview (pictured below) with a preview value of 1. This variable can be injected into a script to change the preview behavior that is seen in the Preview panel. The Preview panel is used to display previews of the data in a selected table or node. There may be times when you want to disable the preview in order to save time or resources. In this case, you can add the SQL Query node to the data source node and enter a simple script to prevent the preview from loading.
- Click here to learn more about the Preview panel.
Using the following expression, you can stop the preview from loading. This is useful if you don't want to see a preview for every change you make, which can be time consuming.
select * from data where @IsPreview = 0
By using the above expression, the IsPreview variable is injected into the script; because the actual value of the variable is 1, not 0, no preview is returned, as pictured below.
